from database.mysqlhelper import MySqLHelper

db = MySqLHelper()


# 添加一条
# data {}
#   `f_code` varchar(255) NOT NULL COMMENT '编码',
#   `f_rq` date NOT NULL COMMENT '日期',
#   `f_macd` varchar(255) NOT NULL COMMENT 'macd',
#   `f_ema12` varchar(255) NOT NULL COMMENT 'ema12',
#   `f_ema26` varchar(255) NOT NULL COMMENT 'ema26',
#   `f_dea` varchar(255) NOT NULL COMMENT 'dea',
def insert(data):
    # 增加多条
    sql1 = 'insert into t_stock_month_macd (f_code,f_rq,f_macd,f_ema12,f_ema26,f_dea) ' \
           'VALUES (%s, %s, %s, %s, %s, %s)'
    li = (
        data['code'], data['rq'], float(data['macd']), float(data['ema12']),
        float(data['ema26']), float(data['dea']))
    count = db.insertone(sql1, li)
    return count


# 添加多条
# data = []
def insert_multiple(list):
    if not list or len(list) == 0:
        return 0
    # 增加多条
    sql1 = 'insert into t_stock_month_macd (f_code,f_rq,f_macd,f_ema12,f_ema26,f_dea) ' \
           'VALUES (%s, %s, %s, %s, %s, %s)'
    li = []
    for data in list:
        li.append((
            data['code'], data['rq'], float(data['macd']), float(data['ema12']),
            float(data['ema26']), float(data['dea'])))
    count = db.insertmany(sql1, li)
    return count


# 获取最新的一条
def select(code):
    # 增加多条
    sql1 = 'select f_code,f_rq,f_macd,f_ema12,f_ema26,f_dea,f_update_time ' \
           'from t_stock_month_macd where f_code = %s order by f_rq desc limit 1 '
    return format_one(db.selectone(sql1, code))


# 统计条数
def select_count(code):
    sql1 = 'select count(1) from t_stock_month_macd where f_code = %s '
    return db.selectone(sql1, code)[0]


# 获取日期值
def select_rq(code, rq):
    sql1 = 'select f_code,f_rq,f_macd,f_ema12,f_ema26,f_dea,f_update_time ' \
           'from t_stock_month_macd where f_code = %s and f_rq = %s '
    return format_one(db.selectone(sql1, (code, rq)))


# 获取最新n条
def select_newest_size(code, size):
    sql1 = 'select f_code,f_rq,f_macd,f_ema12,f_ema26,f_dea,f_update_time ' \
           'from t_stock_month_macd where f_code = %s order by f_rq desc limit %s '
    return format_res(db.selectall(sql1, (code, size)))


# 获取区间
def select_interval(code, rq, size):
    sql1 = 'select f_code,f_rq,f_macd,f_ema12,f_ema26,f_dea,f_update_time ' \
           'from t_stock_month_macd where f_code = %s and f_rq <= %s order by f_rq desc limit %s '
    return format_res(db.selectall(sql1, (code, rq, size)))


# 更新数据
def update(data):
    sql1 = 'update t_stock_month_macd set f_macd = %s, f_ema12 = %s, f_ema26 = %s, f_dea = %s ' \
           'where f_code = %s and f_rq = %s '
    return db.update(sql1, (
        data['macd'], data['ema12'], data['ema26'], data['dea'], data['code'], data['rq']))


# 更新数据
def update_month(data, year_month):
    sql1 = 'update t_stock_month_macd set f_rq = %s, f_macd = %s, f_ema12 = %s, f_ema26 = %s, f_dea = %s ' \
           'where f_code = %s and left(f_rq,7) = %s '
    return db.update(sql1, (
        data['rq'], data['macd'], data['ema12'], data['ema26'], data['dea'], data['code'], year_month))


# 格式化结果
def delete_all(code):
    sql1 = 'delete from  t_stock_month_macd where f_code = %s '
    return db.delete(sql1, (code))


# 格式化结果
def format_one(res):
    if not res:
        return {}
    return {'code': str(res[0], encoding="utf-8"), 'rq': res[1], 'macd': float(res[2]), 'ema12': float(res[3]),
            'ema26': float(res[4]), 'dea': float(res[5]), 'up_time': res[6]}


# 格式化结果
def format_res(res):
    if not res or len(res) == 0:
        return []
    list = []
    for p in res:
        list.append(format_one(p))
    return list


if __name__ == '__main__':
    print('start')
    res = select_interval('600031', '2021-06-20', 10)
    print('ss')
